# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
# 文件：pro_file
获取sftp文件
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
import os
from pycacho.cachobase.download_url_file import download_file
from pythonlession.recordmodule.zipfile_model import open_file,write_file
from pycacho.cachobase.deal_time import add_date,minus_date
from pythonlession.recordmodule.paramiko_model import SftpDeal
from msydwork.prod.pro_config import ip,sftp_user,sftp_password,cursor
from pycacho.cachobase.logger import Logger
logger = Logger("pro_file").get_log()

sftp = SftpDeal(ip, sftp_user, sftp_password)
remote_base = '/data/sftp/hb/home/upload/hb/send/'
local_path = 'E:\zenglingwei\\file\msydloan\\'

# 文件类型、文件对应省份、文件日期 20191227
def down_file(file_type,prov,date):
    if file_type == '01' or file_type == '02' or file_type == '04':      # 分省业务办理确认文件、分省提前清贷/退货文件、分省退货结算文件
        file_name = 'HBCBF_' + prov + '_888190900238401_' + file_type +'_' + date + '_001.DAT'

    elif file_type =='SETTLE':                                          # 分省办理结算订单对账文件
        file_name = 'MPLCASH_' + prov + '_888190900238401_' + file_type +'_' + date + '_001.DAT.old'

    elif file_type =='LOANCONTRACT' or file_type =='PICKIMG':           # 分省借款合同、分省取货照片
        file_name = 'MPLCASH_' + prov + '_888190900238401_' + file_type +'_' + date + '.ZIP'

    elif file_type =='CREDITCONTRACT' or file_type =='IDIMG' or file_type =='LIVEIMG':   # 授信合同、身份证照片、活体视频截取照片
        file_name = 'MPLCASH_888190900238401_' + file_type +'_' + date + '.ZIP'

    elif file_type =='DEDUCTION':                                          # 批量扣款明细文件生成
        file_name = 'MPLCASH_888190900238401_' + file_type +'_' + date + '_001_clearText.DAT'

    else:                                                               # BACK 批量扣款明细回盘文件
        file_name = file_type + '_MPLCASH_888190900238401_DEDUCTION_' + date + '_001.DAT'

    #print('压缩文件：',file_name)
    remote_file = remote_base + date + '/' + file_name
    try:
        local_file = local_path + file_name
        sftp.download(remote_file, local_file)
        return local_file
    except:
        logger.info(' %s 下载出现异常 .....', remote_file)

# 借款信息
def get_loan_info(process_no):
    sql = 'select l.process_no,l.brw_ord_no,l.dep_prov_no,l.brw_ord_dt,p.usr_id_name from pro_hb_loan l,pro_main p where l.process_no=p.process_no and l.process_no= "%s" ;' %(process_no)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        for data in result:
            try:
                data = add_date(data,1,'s')
            except:
                data =str(data)
            info.append(data)
    #print(info)
    return(info)

# 获取已签章合同信息
def get_sign_info(process_no):
    sql = 'select "%s",brw_ord_no,name,id_card,oss_url from pro_sign_info where brw_ord_no =(select brw_ord_no from pro_hb_loan where process_no ="%s");' %(process_no,process_no)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        for data in result:
            try:
                data = add_date(data,1,'s')
            except:
                data =str(data)
            info.append(data)
    #print(info)
    return(info)

# 获取已签章合同信息
def get_img_info(process_no,img_type):
    if img_type == 'block':
        sql = 'select substr(create_time,1,4),substr(create_time,6,2),group_picture_id from pkg_notice where process_no ="%s";' %(process_no)
    else:
        sql = 'select SUBSTR(create_time, 1, 4), SUBSTR(create_time, 6, 2),id_card_front_id, id_card_back_id, live_picture_id from pro_hb_image where process_no ="%s";' % (process_no)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        for data in result:
            info.append(data)
    #print(info)
    return(info)

def batch_down():
    file_types = [
        #'01','02','04',
        'SETTLE',
        #'DEDUCTION','BACK',
        #'PICKIMG',
        # 'CREDITCONTRACT','IDIMG','LIVEIMG'
    ]
    provs = ['05','18',
             #'21','23','28'
             ]

    for file_type in file_types:
        for prov in provs:
            down_file(file_type, prov, '20201218')

# 批量获取合同
def batch_contract():
    total_file = local_path + minus_date(0,'s') + '合同.zip'
    info = ['LW20201028HB000015',
            #'LW20200530HB000041','LW20191229HB000108', 'LW20200621HB000093',
           # '', '', '', '','','', '','',
            ]
    for process_no in info:
        sign_info = get_sign_info(process_no)
        if len(sign_info) == 0:
            logger.info('%s 查无签署合同信息，开始下载sftp压缩包合同.....',process_no)

            loan_info = get_loan_info(process_no)
            zip_file = down_file('LOANCONTRACT', loan_info[2], loan_info[3])
            un_sign_file = local_path + '无签章_' + process_no + '_' + loan_info[4] + '.pdf'
            open_file(zip_file, local_path, loan_info[1], un_sign_file)
            if os.path.exists(un_sign_file):
                print(un_sign_file,'下载成功！')
                write_file(total_file,un_sign_file,os.path.basename(un_sign_file))
        else:
            sign_file =local_path + '有签章_' + process_no + '_' + sign_info[2] + '.pdf'
            download_file(sign_info[4],sign_file)
            print(sign_file,'下载成功！')
            write_file(total_file, sign_file, os.path.basename(sign_file))

# 下载 credit,block 图片
def image_down(img_type):
    info = ['CW20200802HB000650', 'CW20200802HB000651', '', '',]
    for process_no in info:
        img_info = get_img_info(process_no, img_type)
        if img_type=='block':
            try:
                file_name = img_type + '/' +img_info[0]+'/'+img_info[1]+'/'+img_info[2]
                sftp.download(remote_base+file_name, local_path+img_info[2])
            except:
                print(process_no,' 查无该图片!')
        else:
            try:
                card_front = img_type + '/' +img_info[0]+'/'+img_info[1]+'/'+img_info[2]
                sftp.download(remote_base+card_front, local_path+img_info[2])
                card_back = img_type + '/' +img_info[0]+'/'+img_info[1]+'/'+img_info[3]
                sftp.download(remote_base+card_back, local_path+img_info[3])
                live_picture = img_type + '/' +img_info[0]+'/'+img_info[1]+'/'+img_info[4]
                sftp.download(remote_base+live_picture, local_path+img_info[4])
            except:
                print(process_no,' 查无该图片!')


if __name__ == '__main__':
    #down_file('CREDITCONTRACT', '18', '20191227')
    batch_down()
    #batch_contract()
    #get_info('LW20191225HB000002')
    #get_sign_info('LW20191225HB000002')
    #get_img_info('CW20190920HB000003','credit')
    #image_down('credit')    # block,credit







